use serde::Deserialize;
use sqlx::{Executor, SqliteConnection};

use super::get_pool;
use crate::error::SqlxErr;
use sqlx::Row;

const CREATE_SQL: &'static str = r#"
CREATE TABLE IF NOT EXISTS tb_cargo_config (
    id INTEGER PRIMARY KEY AUTOINCREMENT, 
    device_id INTEGER NOT NULL, 
    col INTEGER NOT NULL, 
    row INTEGER NOT NULL, 
    product_id INTEGER NOT NULL,
    UNIQUE(device_id, col, row),
    FOREIGN KEY (product_id) REFERENCES tb_product(id) ON DELETE CASCADE,
    FOREIGN KEY (device_id) REFERENCES tb_device(id) ON DELETE CASCADE
)
"#;

pub async fn init() -> Result<(), SqlxErr> {
    get_pool().execute(CREATE_SQL).await?;
    Ok(())
}

#[derive(Debug, Deserialize)]
pub struct CargoConfig {
    pub col: u8,
    pub row: u8,
    pub product_id: i64,
}

#[derive(Debug, Deserialize)]
pub struct DeviceCargoConfig {
    pub device_id: i64,
    pub cfgs: Box<[CargoConfig]>,
}

pub async fn create_or_update(device_cargo_config: &DeviceCargoConfig) -> Result<(), SqlxErr> {
    let mut tx = get_pool().begin().await?;
    for info in device_cargo_config.cfgs.iter() {
        if let Some(id) =
            is_exists(&mut *tx, device_cargo_config.device_id, info.col, info.row).await?
        {
            update(&mut *tx, id, info.product_id).await?;
        } else {
            create_impl(
                &mut *tx,
                device_cargo_config.device_id,
                info.col,
                info.row,
                info.product_id,
            )
            .await?;
        }
    }
    tx.commit().await?;

    Ok(())
}

async fn create_impl(
    tx: &mut SqliteConnection,
    device_id: i64,
    col: u8,
    row: u8,
    product_id: i64,
) -> Result<(), SqlxErr> {
    sqlx::query(
        r#"
        INSERT INTO tb_cargo_config (device_id, col, row, product_id) VALUES (?, ?, ?, ?)
    "#,
    )
    .bind(device_id)
    .bind(col)
    .bind(row)
    .bind(product_id)
    .execute(tx)
    .await?;
    Ok(())
}

async fn update(tx: &mut SqliteConnection, id: i64, product_id: i64) -> Result<(), SqlxErr> {
    sqlx::query(
        r#"
        UPDATE tb_cargo_config SET product_id = ? WHERE id = ?
    "#,
    )
    .bind(product_id)
    .bind(id)
    .execute(tx)
    .await?;
    Ok(())
}

async fn is_exists(
    tx: &mut SqliteConnection,
    device_id: i64,
    col: u8,
    row: u8,
) -> Result<Option<i64>, SqlxErr> {
    let ret =
        sqlx::query(r#"SELECT id FROM tb_cargo_config WHERE device = ? AND col = ? AND row = ?"#)
            .bind(device_id)
            .bind(col)
            .bind(row)
            .fetch_optional(tx)
            .await?;
    Ok(ret.and_then(|row| row.get(0)))
}
